/*
 * FindSeatsCommand.java
 *
 * Created on October 4, 2007, 5:28 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package drb;

import java.io.BufferedReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;

/**
 *
 * @author Asger
 */
public class FindSeatsCommand extends PromptingCommand {
    
    private HashSet<Seat> selectedSeats = new HashSet<Seat>();
    private TrainRoute selectedRoute = new TrainRoute();
    
    public Ticket ticket;
    
    public HashSet<Seat> getSelectedSeats() {
        return selectedSeats;
    }
    
    public TrainRoute getSelectedRoute() {
        return selectedRoute;
    }
    
    /** Creates a new instance of FindSeatsCommand */
    public FindSeatsCommand(BufferedReader input) {
        super(input);
    }
    
    public void execute() throws SQLException {        
        boolean aisle = promptYesNo("Must the seat be by the aisle?", false);
        boolean window = promptYesNo("Must be seat be by a window?", false);
        boolean freeNeighbour = promptYesNo("Must there be a free seat next to the seat?", false);
        
        // Get the route associated with this ticket
        TrainRoute route = ticket.route;
        
        int numTransfers = route.getNumberOfTransfers();
        
        System.out.println("-------------------------------");
        System.out.println("Connection: " + ticket.route);
        
        //-----------------------------------------------------
        //  Select a single segment if there is more than one
        //-----------------------------------------------------
        if (numTransfers > 0)
        {
            System.out.println("Your ticket is valid for the following routes:");
            
            ArrayList<TrainRoute> segments = route.getRouteSegments();
            ArrayList<Boolean> booked = new ArrayList<Boolean>();
            boolean hasOneUnbooked = false;
            
            for (int i=0; i<segments.size(); i++)
            {                
                TrainRoute r = segments.get(i);
                boolean isBooked = r.hasReservationByTicket(ticket.id, connection);
                hasOneUnbooked = !isBooked || hasOneUnbooked;
                booked.add(isBooked);
                if (isBooked)
                    System.out.println("  " + (i+1) + ". " + segments.get(i) + " (Already booked)");
                else
                    System.out.println("  " + (i+1) + ". " + segments.get(i));
            }
            if (!hasOneUnbooked)
            {
                System.out.println("You cannot reserve any more seats for this ticket.");
                return;
            }
            System.out.println("Please enter the what part you wish to find a seat for: (1-" + (numTransfers+1)+")");
            int newRoute = promptInt(1, numTransfers+1) - 1;
            
            if (booked.get(newRoute))
            {
                System.out.println("You already have a seat reserved for this route.");
                return;
            }
            
            route = segments.get(newRoute);
        }
        else if (route.hasReservationByTicket(ticket.id, connection))
        {
            System.out.println("You cannot reserve any more seats for this ticket.");
            return;
        }
        
        // Find free seats along the route
        HashSet<Seat> seats = new HashSet<Seat>();
        ArrayList<Seat> seatsOnConnection = getFreeSeatsOnConnection(route.connections.get(0), ticket.firstClass, aisle, window, freeNeighbour);
        
        seats.addAll(seatsOnConnection);
        
        for (int i=1; i<route.connections.size(); i++)
        {
            seatsOnConnection = getFreeSeatsOnConnection(route.connections.get(i), ticket.firstClass, aisle, window, freeNeighbour);
            seats.retainAll(seatsOnConnection);
        }
        
        if (seats.size() == 0)
        {
            System.out.println("-------------------------------");
            System.out.println("Sorry, but no suitable seats were found.");
            System.out.println("-------------------------------");
        }
        else
        {
            System.out.println("-------------------------------");
            System.out.println("Found " + seats.size() + " suitable seats.");
            System.out.println("-------------------------------");
            
            // Sort the seats by wagon, row, and then position
            ArrayList<Seat> seatList = new ArrayList<Seat>();
            seatList.addAll(seats);
            Collections.sort(seatList);
            
            int wagonNumber = -1;
            int rowNum = -1;
            for (Seat seat : seatList)
            {
                seat.fetchDetails(connection);
                if (rowNum != seat.row) {
                    rowNum = seat.row;
                    System.out.println("");
                }
                if (wagonNumber != seat.wagonNumber) {
                    wagonNumber = seat.wagonNumber;
                    System.out.println("Wagon " + wagonNumber+":");
                }
                System.out.print("" + seat.row + seat.position + " ");
            }
            System.out.println("\n-------------------------------");
        }
        
        selectedSeats = seats;
        selectedRoute = route;
    }
    
    public ArrayList<Seat> getFreeSeatsOnConnection(TrainConnection con, boolean firstClass, boolean aisle, boolean window, boolean freeNeighbour) throws SQLException
    {
        String placement = "";
        if (aisle && window)
            placement = "A,W";
        else if (aisle)
            placement = "A";
        else if (window)
            placement = "W";
        char fcChar = firstClass? 'A':'B';
        String sql = "SELECT * FROM seats s NATURAL JOIN wagons w NATURAL JOIN transports t NATURAL JOIN connections c WHERE " +
                "c.departure="+con.departure+" AND c.train_number="+con.trainNumber+" AND s.seat_placement LIKE '%"+placement+"%' AND " +
                "s.class='"+fcChar+"' AND c.to_name='"+escapeSQL(con.to)+"' AND c.from_name='"+escapeSQL(con.from)+"' AND " +
                "NOT EXISTS (SELECT * FROM reservations r WHERE r.wagon_number=s.wagon_number AND r.seat_row=s.seat_row AND " +
                        "r.seat_position=s.seat_position AND r.departure="+con.departure+")";
        if (freeNeighbour) {
            // ..And there exists a neighbour seat for which there exists no reservation
            sql += " AND EXISTS (SELECT * FROM neighbours n WHERE " +
                        "n.wagon_number=s.wagon_number AND " +
                        "((n.position1=s.seat_position AND n.row1=s.seat_row) OR (n.position2=s.seat_position AND n.row2=s.seat_row)) AND " +
                        "NOT EXISTS (SELECT * FROM reservations r WHERE r.wagon_number=s.wagon_number AND r.departure=c.departure AND " +
                            "((r.seat_position=n.position1 AND r.seat_row=n.row1) OR " +
                            " (r.seat_position=n.position2 AND r.seat_row=n.row2))))";
        }
        ResultSet result = connection.createStatement().executeQuery(sql);
        
        ArrayList<Seat> seats = new ArrayList<Seat>();
        while (result.next()) {
            Seat seat = Seat.parse(result, "s.");
            seats.add(seat);
        }
        return seats;
    }

    public void reset() {
        selectedSeats = new HashSet<Seat>();
        selectedRoute = new TrainRoute();
    }
}





